﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace TaxiDispatchClient.Service
{
   public  class ExcelCommon
    {
        public static double CompletePts;
        public static void OutPutExcel(DataSet ds, string title)
        {
            CompletePts = 0;
            if (ds == null) return;
            Excel.Application exapp = new Excel.Application();
            Excel.Range range = null;
            Excel.Workbooks objBooks = exapp.Workbooks;
            Excel.Workbook objbook = objBooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet = objbook.Sheets[1];

            //写表格标题
            worksheet.Cells[1, 1] = title;
            range = (Excel.Range)worksheet.Cells[1, 1];
            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 居中
            range.EntireColumn.AutoFit();//自动调整列宽
            range.Interior.Color = Color.DarkOrange;   // 背景颜色           
            range.Font.Color = Color.Black;
            range.Font.Size = 13;                   // 文字大小
            range.Font.Bold = true;                 // 粗体显示
            range.BorderAround(Excel.XlLineStyle.xlContinuous,  // 加边框
            Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
            //合并标题表格
            int columnCount = ds.Tables[0].Columns.Count;
            string strColumn = ((char)(columnCount + 64)).ToString();
            range = worksheet.get_Range("A1", strColumn + "1");
            range.Merge();
            //写列名
            for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
            {
                worksheet.Cells[2, i + 1] = ds.Tables[0].Columns[i].ColumnName; //获取列名      
                range = (Excel.Range)worksheet.Cells[2, i + 1];
                //  range.Interior.Color = Color.DarkGray;
                range.Font.Bold = true;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 居中
                //加边框
                range.BorderAround(Excel.XlLineStyle.xlContinuous,
                    Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic,
                     null);
                //range.ColumnWidth = 4.63;//设置列宽
                range.EntireColumn.AutoFit();//自动调整列宽
                //r1.EntireRow.AutoFit();//自动调整行高
            }
            //写入内容
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                System.Threading.Thread.Sleep(5);
                CompletePts = (double)i/ds.Tables[0].Rows.Count;
                for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                {
                    worksheet.Cells[i + 3, j + 1] = ds.Tables[0].Rows[i][j];
                    range = (Excel.Range)worksheet.Cells[3 + i, j + 1];
                    range.Font.Size = 10;
                    //加边框
                    range.BorderAround(Excel.XlLineStyle.xlContinuous,
                        Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic,
                         null);
                    range.EntireColumn.AutoFit();//自动调整列宽
                }
            }
            exapp.Visible = true;
        }


         //加载Excel    
        public static DataSet LoadDataFromExcel(string filePath)  
        {  
            try  
            {  
                 string strConn;  
                 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";  
                 OleDbConnection OleConn = new OleDbConnection(strConn);  
                 OleConn.Open();  
                 String sql = "SELECT * FROM  [Sheet1$]";//可是更改Sheet名称，比如sheet2，等等    
   
                 OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);  
                 DataSet OleDsExcle = new DataSet();  
                 OleDaExcel.Fill(OleDsExcle, "Sheet1");  
                 OleConn.Close();  
                 return OleDsExcle;  
             }  
             catch (Exception err)  
             {  
       
                 return null;  
             }  
        }  

    }
}
